using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;

using MakeIT.Framework.Data;
using MakeIT.Alf.Site.Entidades;

namespace MakeIT.Alf.Site.Negocios
{
	public partial class AlfDAO : DAO
	{

		public static ImovelVO getImovel(int? idImovel, string titulo)
		{
			ImovelVO imovelVO = null;
			List<ImovelVO> listImovelVOs = getImovels(idImovel, titulo, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
			if (listImovelVOs != null && listImovelVOs.Count > 0)
			{
				imovelVO = listImovelVOs[0];
			}
			return imovelVO;
		}

		public static ImovelVO getImovel(int? idImovel)
		{
			return getImovel(idImovel, null);
		}

		public static List<ImovelVO> getImovels()
		{
			return getImovels(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
		}

		public static List<ImovelVO> getImovels(int? idImovel)
		{
			return getImovels(idImovel, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
		}

		public static List<ImovelVO> getImovels(int? idImovel, string titulo)
		{
			return getImovels(idImovel, titulo, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
		}

        public static List<ImovelVO> getImovels(int? idImovel, string titulo, int? idPromotor, int? idIndicador, int? idCliente, int? idImovelCat, string imediacoes, string zelador, string vago, bool? permuta, string placa, string chaves, string visitas, bool? vda, bool? loc, decimal? vlrVda, decimal? vlrLoc, bool? ativo)
        {
            return getImovels(idImovel, titulo, idPromotor, idIndicador, idCliente, idImovelCat, imediacoes, zelador, vago, permuta, placa, chaves, visitas, vda, loc, vlrVda, vlrLoc, null, ativo);
        }

        public static List<ImovelVO> getImovels(int? idImovel, string titulo, int? idPromotor, int? idIndicador, int? idCliente, int? idImovelCat, string imediacoes, string zelador, string vago, bool? permuta, string placa, string chaves, string visitas, bool? vda, bool? loc, decimal? vlrVda, decimal? vlrLoc, char? destaque, bool? ativo)
        {
            return getImovels(idImovel, titulo, idPromotor, idIndicador, idCliente, idImovelCat, imediacoes, zelador, vago, permuta, placa, chaves, visitas, vda, loc, vlrVda, vlrLoc, destaque, null, ativo);
        }

        public static List<ImovelVO> getImovels(int? idImovel, string titulo, int? idPromotor, int? idIndicador, int? idCliente, int? idImovelCat, string imediacoes, string zelador, string vago, bool? permuta, string placa, string chaves, string visitas, bool? vda, bool? loc, decimal? vlrVda, decimal? vlrLoc, char? destaque, int? limite, bool? ativo)
        {
            return getImovels(idImovel, titulo, idPromotor, idIndicador, idCliente, idImovelCat, imediacoes, zelador, vago, permuta, placa, chaves, visitas, vda, loc, vlrVda, vlrLoc, null, null, null, null, null, null, null, null, destaque, limite, null, ativo);
        }

        public static List<ImovelVO> getImovels(int? idImovel, string titulo, int? idPromotor, int? idIndicador, int? idCliente, int? idImovelCat, string imediacoes, string zelador, string vago, bool? permuta, string placa, string chaves, string visitas, bool? vda, bool? loc, decimal? vlrVda, decimal? vlrLoc, int? dorms, int? vagas, double? valorMax, double? valorMin, int? idImovelTipo, int? bairro, int? cidade, int? estado, char? destaque, int? limite, char? random, bool? ativo)
		{
			string where = "WHERE ";
			StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT imo.idImovel as imo_idImovel, imo.titulo as imo_titulo, imo.descricao as imo_descricao, imo.ativo as imo_ativo, imo.idLogradouro as imo_idLogradouro, ");
            query.AppendLine("       imo.endNum as imo_endNum, imo.endCom as imo_endCom, imo.imediacoes as imo_imediacoes, imo.andares as imo_andares, ");
            query.AppendLine("       imo.aptoAndar as imo_aptoAndar, imo.aptoPos as imo_aptoPos, imo.condominio as imo_condominio, imo.zelador as imo_zelador, ");
            query.AppendLine("       imo.quartos as imo_quartos, imo.suites as imo_suites, imo.areaTotal as imo_areaTotal, imo.areaUtil as imo_areaUtil, ");
            query.AppendLine("       imo.vagasGaragem as imo_vagasGaragem, imo.vago as imo_vago, imo.face as imo_face, imo.financiamento as imo_financiamento, ");
            query.AppendLine("       imo.fGTS as imo_fGTS, imo.permuta as imo_permuta, imo.vlrCond as imo_vlrCond, imo.placa as imo_placa, imo.chaves as imo_chaves, ");
            query.AppendLine("       imo.visitas as imo_visitas, imo.obs as imo_obs, imo.idPromotor as imo_idPromotor, pes.nome as pes_nome, imo.idIndicador as imo_idIndicador, ");
            query.AppendLine("       pes2.nome as pes2_nome, imo.idCliente as imo_idCliente, pes3.nome as pes3_nome, imo.vda as imo_vda, imo.loc as imo_loc, ");
            query.AppendLine("       imo.vlrVda as imo_vlrVda, imo.vlrLoc as imo_vlrLoc, imo.idImovelCat as imo_idImovelCat, imo.destaque as imo_destaque, ");
            query.AppendLine("       lgd.logradouro as lgd_logradouro, ");
            query.AppendLine("       imo.Cadastro as imo_Cadastro, imo.CadastradoPor as imo_CadastradoPor, imo.Edicao as imo_Edicao, imo.EditadoPor as imo_EditadoPor  ");
			query.AppendLine(" FROM imovel as imo");
            query.AppendLine("      LEFT OUTER JOIN pessoa as pes on imo.idPromotor=pes.idPessoa ");
            query.AppendLine("      LEFT OUTER JOIN pessoa as pes2 on imo.idIndicador=pes2.idPessoa ");
            query.AppendLine("      LEFT OUTER JOIN pessoa as pes3 on imo.idCliente=pes3.idPessoa ");
            query.AppendLine("      LEFT OUTER JOIN imovelcat as imvc on imo.idImovelCat=imvc.idImovelCat  ");
            query.AppendLine("          LEFT OUTER JOIN imoveltipo as imvt on imvc.idImovelTipo=imvt.idImovelTipo  ");
            query.AppendLine("      LEFT OUTER JOIN logradouro as lgd on imo.idLogradouro=lgd.idLogradouro  ");
            query.AppendLine("          LEFT OUTER JOIN bairro as bai on lgd.idBairro=bai.idBairro  ");
            query.AppendLine("              LEFT OUTER JOIN cidade as cid on bai.idCidade=cid.idCidade  ");

            if (idImovel != null)
            {
                query.AppendLine(where + "imo.idImovel=@idImovel ");
                where = "AND ";
            }
            else
            {
                if (titulo != null)
                {
                    query.AppendLine(where + "imo.titulo LIKE @titulo ");
                    where = "AND ";
                }
                if (imediacoes != null)
                {
                    query.AppendLine(where + "imo.imediacoes=@imediacoes ");
                    where = "AND ";
                }
                if (zelador != null)
                {
                    query.AppendLine(where + "imo.zelador=@zelador ");
                    where = "AND ";
                }
                if (vago != null)
                {
                    query.AppendLine(where + "imo.vago=@vago ");
                    where = "AND ";
                }
                if (permuta != null)
                {
                    query.AppendLine(where + "imo.permuta=@permuta ");
                    where = "AND ";
                }
                if (placa != null)
                {
                    query.AppendLine(where + "imo.placa=@placa ");
                    where = "AND ";
                }
                if (chaves != null)
                {
                    query.AppendLine(where + "imo.chaves=@chaves ");
                    where = "AND ";
                }
                if (visitas != null)
                {
                    query.AppendLine(where + "imo.visitas=@visitas ");
                    where = "AND ";
                }
                if (idPromotor != null)
                {
                    query.AppendLine(where + "imo.idPromotor=@idPromotor ");
                    where = "AND ";
                }
                if (idIndicador != null)
                {
                    query.AppendLine(where + "imo.idIndicador=@idIndicador ");
                    where = "AND ";
                }
                if (idCliente != null)
                {
                    query.AppendLine(where + "imo.idCliente=@idCliente ");
                    where = "AND ";
                }
                if (vda != null && (bool)vda)
                {
                    query.AppendLine(where + " ( imo.vda='S' ) ");
                    where = "AND ";
                }
                if (loc != null && (bool)loc)
                {
                    query.AppendLine(where + " ( imo.loc='S' ) ");
                    where = "AND ";
                }
                if (vlrVda != null)
                {
                    query.AppendLine(where + "imo.vlrVda=@vlrVda ");
                    where = "AND ";
                }
                if (vlrLoc != null)
                {
                    query.AppendLine(where + "imo.vlrLoc=@vlrLoc ");
                    where = "AND ";
                }
                if (idImovelTipo != null && idImovelTipo != -1)
                {
                    query.AppendLine(where + " imvt.idImovelTipo=@idImovelTipo ");
                    where = "AND ";
                }
                if (dorms != null)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "imo.quartos=@doms ");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (vagas != null)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "imo.vagasGaragem=@vagas ");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (valorMin != null)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "imo.vlrVda > @vlrMin ");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (valorMax != null)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "imo.vlrVda < @vlrMax ");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (idImovelCat != null)
                {
                    query.AppendLine(where + "imo.idImovelCat=@idImovelCat ");
                    where = "AND ";
                }
                if (bairro != null && bairro != -1)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "bai.idBairro=@bairro");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (cidade != null && cidade != -1)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "cid.idCidade=@cidade");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (estado != null && estado != -1)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "cid.idEstado=@estado");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (destaque != null)
                {
                    if (destaque == 'S')
                    {
                        query.AppendLine(where + "imo.destaque='S' ");
                        where = "AND ";
                    }
                    if (destaque == 'N')
                    {
                        query.AppendLine(where + "imo.destaque='N' ");
                        where = "AND ";
                    }
                }
            }

            if (ativo != null)
            {
                query.AppendLine(where + "imo.ativo=@ativo ");
                where = "AND ";
            }

            if (random == 'S')
            {
                query.AppendLine("ORDER BY RAND() ");
            }
            else
            {
			    query.AppendLine("ORDER BY imo.titulo ");
            }
            if (limite != null)
            {
                query.AppendLine(" limit 0," + limite);
            }


			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

            if (idImovel != null)
            {
                cmd.Parameters.Add(getParameter(cmd, "@idImovel", idImovel));
            }
            else
            {
                if (titulo != null) cmd.Parameters.Add(getParameter(cmd, "@titulo", "%" + titulo + "%"));
                if (imediacoes != null) cmd.Parameters.Add(getParameter(cmd, "@imediacoes", imediacoes));
                if (zelador != null) cmd.Parameters.Add(getParameter(cmd, "@zelador", zelador));
                if (vago != null) cmd.Parameters.Add(getParameter(cmd, "@vago", vago));
                if (permuta != null) cmd.Parameters.Add(getParameter(cmd, "@permuta", permuta.Value ? "S" : "N"));
                if (placa != null) cmd.Parameters.Add(getParameter(cmd, "@placa", placa));
                if (chaves != null) cmd.Parameters.Add(getParameter(cmd, "@chaves", chaves));
                if (visitas != null) cmd.Parameters.Add(getParameter(cmd, "@visitas", visitas));
                if (idPromotor != null) cmd.Parameters.Add(getParameter(cmd, "@idPromotor", idPromotor));
                if (idIndicador != null) cmd.Parameters.Add(getParameter(cmd, "@idIndicador", idIndicador));
                if (idCliente != null) cmd.Parameters.Add(getParameter(cmd, "@idCliente", idCliente));
                if (vda != null) cmd.Parameters.Add(getParameter(cmd, "@vda", vda.Value ? "S" : "N"));
                if (loc != null) cmd.Parameters.Add(getParameter(cmd, "@loc", loc.Value ? "S" : "N"));
                if (vlrVda != null) cmd.Parameters.Add(getParameter(cmd, "@vlrVda", vlrVda));
                if (vlrLoc != null) cmd.Parameters.Add(getParameter(cmd, "@vlrLoc", vlrLoc));
                if (idImovelCat != null) cmd.Parameters.Add(getParameter(cmd, "@idImovelCat", idImovelCat));
                if (idImovelTipo != null) cmd.Parameters.Add(getParameter(cmd, "@idImovelTipo", idImovelTipo));
                if (dorms != null) cmd.Parameters.Add(getParameter(cmd, "@doms", dorms));
                if (vagas != null) cmd.Parameters.Add(getParameter(cmd, "@vagas", vagas));
                if (valorMin != null) cmd.Parameters.Add(getParameter(cmd, "@vlrMin", valorMin));
                if (valorMax != null) cmd.Parameters.Add(getParameter(cmd, "@vlrMax", valorMax));
                if (bairro != null && bairro != -1) cmd.Parameters.Add(getParameter(cmd, "@bairro", bairro));
                if (cidade != null && cidade != -1) cmd.Parameters.Add(getParameter(cmd, "@cidade", cidade));
                if (estado != null && estado != -1) cmd.Parameters.Add(getParameter(cmd, "@estado", estado));
            }
            if (ativo != null) cmd.Parameters.Add(getParameter(cmd, "@ativo", ativo.Value ? "S" : "N"));
            

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();

				List<ImovelVO> listImovel = new List<ImovelVO>();

				while (reader.Read())
				{
					ImovelVO imovelVO = ImovelVO.CreateVO();
					imovelVO.idImovel = reader.GetInt32(reader.GetOrdinal("imo_idImovel"));
					imovelVO.titulo = reader.GetString(reader.GetOrdinal("imo_titulo"));
					imovelVO.descricao = reader.GetString(reader.GetOrdinal("imo_descricao"));
                    imovelVO.ativo = reader.GetString(reader.GetOrdinal("imo_ativo")).Equals("S");
                    imovelVO.idLogradouro = reader.GetInt32(reader.GetOrdinal("imo_idLogradouro"));
                    imovelVO.idLogradouroVO.logradouro = reader.GetString(reader.GetOrdinal("lgd_logradouro"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_endNum")))
						imovelVO.endNum = reader.GetString(reader.GetOrdinal("imo_endNum"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_endCom")))
						imovelVO.endCom = reader.GetString(reader.GetOrdinal("imo_endCom"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_imediacoes")))
						imovelVO.imediacoes = reader.GetString(reader.GetOrdinal("imo_imediacoes"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_andares")))
						imovelVO.andares = reader.GetInt32(reader.GetOrdinal("imo_andares"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_aptoAndar")))
						imovelVO.aptoAndar = reader.GetInt32(reader.GetOrdinal("imo_aptoAndar"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_aptoPos")))
						imovelVO.aptoPos = reader.GetString(reader.GetOrdinal("imo_aptoPos"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_condominio")))
						imovelVO.condominio = reader.GetString(reader.GetOrdinal("imo_condominio"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_zelador")))
						imovelVO.zelador = reader.GetString(reader.GetOrdinal("imo_zelador"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_quartos")))
						imovelVO.quartos = reader.GetInt32(reader.GetOrdinal("imo_quartos"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_suites")))
						imovelVO.suites = reader.GetInt32(reader.GetOrdinal("imo_suites"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_areaTotal")))
						imovelVO.areaTotal = reader.GetDecimal(reader.GetOrdinal("imo_areaTotal"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_areaUtil")))
						imovelVO.areaUtil = reader.GetDecimal(reader.GetOrdinal("imo_areaUtil"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_vagasGaragem")))
						imovelVO.vagasGaragem = reader.GetInt32(reader.GetOrdinal("imo_vagasGaragem"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_vago")))
						imovelVO.vago = reader.GetString(reader.GetOrdinal("imo_vago"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_face")))
						imovelVO.face = reader.GetString(reader.GetOrdinal("imo_face"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_financiamento")))
						imovelVO.financiamento = reader.GetString(reader.GetOrdinal("imo_financiamento"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_fGTS")))
						imovelVO.fGTS = reader.GetString(reader.GetOrdinal("imo_fGTS"));
					string permutatmp = reader.GetString(reader.GetOrdinal("imo_permuta"));
					if(!string.IsNullOrEmpty(permutatmp)) 
					{
						imovelVO.permuta = reader.GetString(reader.GetOrdinal("imo_permuta")).Equals("S");
					}
					if (!reader.IsDBNull(reader.GetOrdinal("imo_vlrCond")))
						imovelVO.vlrCond = reader.GetDecimal(reader.GetOrdinal("imo_vlrCond"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_placa")))
						imovelVO.placa = reader.GetString(reader.GetOrdinal("imo_placa"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_chaves")))
						imovelVO.chaves = reader.GetString(reader.GetOrdinal("imo_chaves"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_visitas")))
						imovelVO.visitas = reader.GetString(reader.GetOrdinal("imo_visitas"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_obs")))
						imovelVO.obs = reader.GetString(reader.GetOrdinal("imo_obs"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_idPromotor")))
						imovelVO.idPromotor = reader.GetInt32(reader.GetOrdinal("imo_idPromotor"));
					if (!reader.IsDBNull(reader.GetOrdinal("pes_nome")))
						imovelVO.idPromotorVO.nome = reader.GetString(reader.GetOrdinal("pes_nome"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_idIndicador")))
						imovelVO.idIndicador = reader.GetInt32(reader.GetOrdinal("imo_idIndicador"));
					if (!reader.IsDBNull(reader.GetOrdinal("pes2_nome")))
						imovelVO.idIndicadorVO.nome = reader.GetString(reader.GetOrdinal("pes2_nome"));
					imovelVO.idCliente = reader.GetInt32(reader.GetOrdinal("imo_idCliente"));
					if (!reader.IsDBNull(reader.GetOrdinal("pes3_nome")))
						imovelVO.idClienteVO.nome = reader.GetString(reader.GetOrdinal("pes3_nome"));
					imovelVO.vda = reader.GetString(reader.GetOrdinal("imo_vda")).Equals("S");
					imovelVO.loc = reader.GetString(reader.GetOrdinal("imo_loc")).Equals("S");
					if (!reader.IsDBNull(reader.GetOrdinal("imo_vlrVda")))
						imovelVO.vlrVda = reader.GetDecimal(reader.GetOrdinal("imo_vlrVda"));
					if (!reader.IsDBNull(reader.GetOrdinal("imo_vlrLoc")))
						imovelVO.vlrLoc = reader.GetDecimal(reader.GetOrdinal("imo_vlrLoc"));
					imovelVO.idImovelCat = reader.GetInt32(reader.GetOrdinal("imo_idImovelCat"));
                    imovelVO.destaque = reader.GetChar(reader.GetOrdinal("imo_destaque"));
                    imovelVO.cadastro = reader.GetDateTime(reader.GetOrdinal("imo_Cadastro"));
					imovelVO.cadastradoPor = reader.GetString(reader.GetOrdinal("imo_CadastradoPor"));
					imovelVO.edicao = reader.GetDateTime(reader.GetOrdinal("imo_Edicao"));
					imovelVO.editadoPor = reader.GetString(reader.GetOrdinal("imo_EditadoPor"));


					listImovel.Add(imovelVO);
				}
				reader.Close();
				return listImovel;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
			}
		}

        public static List<ImovelVO> getImovelsPeqSite(int? idImovel, bool? vda, bool? loc, int? idImovelTipo, string bairros, int? cidade, int? estado, int? doms, int? vagas, decimal? vlrMin, decimal? vlrMax, char? destaque, int? limite, char? random, bool? ativo)
        {
            DbCommand cmd = getCommand();

            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT imo.idImovel as imo_idImovel, imo.titulo as imo_titulo, imo.descricao as imo_descricao, imo.ativo as imo_ativo, imo.idLogradouro as imo_idLogradouro, imo.endNum as imo_endNum, imo.endCom as imo_endCom, imo.imediacoes as imo_imediacoes, imo.andares as imo_andares, imo.aptoAndar as imo_aptoAndar, imo.aptoPos as imo_aptoPos, imo.condominio as imo_condominio, imo.zelador as imo_zelador, imo.quartos as imo_quartos, imo.suites as imo_suites, imo.areaTotal as imo_areaTotal, imo.areaUtil as imo_areaUtil, imo.vagasGaragem as imo_vagasGaragem, imo.vago as imo_vago, imo.face as imo_face, imo.financiamento as imo_financiamento, imo.fGTS as imo_fGTS, imo.permuta as imo_permuta, imo.vlrCond as imo_vlrCond, imo.placa as imo_placa, imo.chaves as imo_chaves, imo.visitas as imo_visitas, imo.obs as imo_obs, imo.idPromotor as imo_idPromotor, pes.nome as pes_nome, imo.idIndicador as imo_idIndicador, pes2.nome as pes2_nome, imo.idCliente as imo_idCliente, pes3.nome as pes3_nome, imo.vda as imo_vda, imo.loc as imo_loc, imo.vlrVda as imo_vlrVda, imo.vlrLoc as imo_vlrLoc, imo.idImovelCat as imo_idImovelCat, imo.destaque as imo_destaque, imo.Cadastro as imo_Cadastro, imo.CadastradoPor as imo_CadastradoPor, imo.Edicao as imo_Edicao, imo.EditadoPor as imo_EditadoPor  ");
            query.AppendLine("FROM imovel as imo " );
            query.AppendLine("      LEFT OUTER JOIN pessoa as pes on imo.idPromotor=pes.idPessoa ");
            query.AppendLine("      LEFT OUTER JOIN pessoa as pes2 on imo.idIndicador=pes2.idPessoa ");
            query.AppendLine("      LEFT OUTER JOIN pessoa as pes3 on imo.idCliente=pes3.idPessoa ");
            query.AppendLine("      LEFT OUTER JOIN imovelcat as imvc on imo.idImovelCat=imvc.idImovelCat  ");
            query.AppendLine("      LEFT OUTER JOIN logradouro as lgd on imo.idLogradouro=lgd.idLogradouro  ");
            query.AppendLine("          LEFT OUTER JOIN bairro as bai on lgd.idBairro=bai.idBairro  ");
            query.AppendLine("              LEFT OUTER JOIN cidade as cid on bai.idCidade=cid.idCidade  ");


            if (idImovel != null)
            {
                query.AppendLine(where + "imo.idImovel=@idImovel ");
                where = "AND ";
            }
            else
            {
                if ((bool)vda)
                {
                    query.AppendLine(where + " ( imo.vda='S' ) ");
                    where = "AND ";
                }
                if ((bool)loc)
                {
                    query.AppendLine(where + " ( imo.loc='S' ) ");
                    where = "AND ";
                }


                if (idImovelTipo != null && idImovelTipo != -1)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    List<ImovelcatVO> listIcVO = AlfDAO.getImovelcats(null, idImovelTipo, null);
                    if((listIcVO ?? new List<ImovelcatVO>()).Count <= 0) query.AppendLine("true");
                    else foreach (ImovelcatVO icVO in AlfDAO.getImovelcats(null, idImovelTipo, null))
                    {
                        query.AppendLine(where + "imo.idImovelCat=" + icVO.idImovelCat + " ");
                        where = "OR ";
                    }
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (!String.IsNullOrEmpty(bairros))
                {
                    string[] bairroLista = bairros.Split(';');
                    query.AppendLine(where + " ( ");
                    where = " ";
                    List<string> bairroAnterior = new List<string>();
                    foreach (string bairroItem in bairroLista)
                    {
                        if (!bairroItem.Equals(bairroAnterior.Find(b => b == bairroItem)))
                        {
                            query.AppendLine(where + "bai.idBairro=@bairro" + bairroItem);
                            where = "OR ";
                            cmd.Parameters.Add(getParameter(cmd, "@bairro" + bairroItem, int.Parse(bairroItem)));
                            bairroAnterior.Add(bairroItem);
                        }
                    }
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (cidade != null && cidade != -1 && cidade != -2)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "cid.idCidade=@cidade");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (estado != null && estado != -1 && estado != -2)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "cid.idEstado=@estado");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (doms != null && doms != -1 && doms != -2)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    if (doms < 4)
                        query.AppendLine(where + "imo.quartos=@doms ");
                    else
                        query.AppendLine(where + "imo.quartos > 3  ");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (vagas != null)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    if (vagas < 4)
                        query.AppendLine(where + "imo.vagasGaragem=@vagas ");
                    else
                        query.AppendLine(where + "imo.vagasGaragem > 3  ");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (vlrMin != null)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "imo.vlrVda > @vlrMin ");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (vlrMax != null)
                {
                    query.AppendLine(where + " ( ");
                    where = " ";
                    query.AppendLine(where + "imo.vlrVda < @vlrMax ");
                    query.AppendLine(" ) ");
                    where = "AND ";
                }
                if (destaque != null)
                {
                    if (destaque == 'S')
                    {
                        query.AppendLine(where + "imo.destaque='S' ");
                        where = "AND ";
                    }
                    if (destaque == 'N')
                    {
                        query.AppendLine(where + "imo.destaque='N' ");
                        where = "AND ";
                    }
                }
            }

            if (ativo != null)
            {
                query.AppendLine(where + "imo.ativo=@ativo ");
                where = "AND ";
            }

            if (random == 'S')
            {
                query.AppendLine("ORDER BY RAND() ");
            }
            else
            {
                query.AppendLine("ORDER BY imo.titulo ");
            }
            if (limite != null)
            {
                query.AppendLine(" limit 0," + limite);
            }


            cmd.CommandText = query.ToString();
            if (idImovel != null)
            {
                cmd.Parameters.Add(getParameter(cmd, "@idImovel", idImovel));
            }
            else
            {
                if (cidade != null && cidade != -1) cmd.Parameters.Add(getParameter(cmd, "@cidade", cidade));
                if (estado != null && estado != -1) cmd.Parameters.Add(getParameter(cmd, "@estado", estado));
                if (doms != null && doms < 4) cmd.Parameters.Add(getParameter(cmd, "@doms", doms));
                if (vagas != null && vagas < 4) cmd.Parameters.Add(getParameter(cmd, "@vagas", vagas));
                if (vlrMin != null) cmd.Parameters.Add(getParameter(cmd, "@vlrMin", vlrMin));
                if (vlrMax != null) cmd.Parameters.Add(getParameter(cmd, "@vlrMax", vlrMax));
            }
            if (ativo != null) cmd.Parameters.Add(getParameter(cmd, "@ativo", ativo.Value ? "S" : "N"));


            DbDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();

                List<ImovelVO> listImovel = new List<ImovelVO>();

                while (reader.Read())
                {
                    ImovelVO imovelVO = ImovelVO.CreateVO();
                    imovelVO.idImovel = reader.GetInt32(reader.GetOrdinal("imo_idImovel"));
                    imovelVO.titulo = reader.GetString(reader.GetOrdinal("imo_titulo"));
                    imovelVO.descricao = reader.GetString(reader.GetOrdinal("imo_descricao"));
                    imovelVO.ativo = reader.GetString(reader.GetOrdinal("imo_ativo")).Equals("S");
                    imovelVO.idLogradouro = reader.GetInt32(reader.GetOrdinal("imo_idLogradouro"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_endNum")))
                        imovelVO.endNum = reader.GetString(reader.GetOrdinal("imo_endNum"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_endCom")))
                        imovelVO.endCom = reader.GetString(reader.GetOrdinal("imo_endCom"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_imediacoes")))
                        imovelVO.imediacoes = reader.GetString(reader.GetOrdinal("imo_imediacoes"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_andares")))
                        imovelVO.andares = reader.GetInt32(reader.GetOrdinal("imo_andares"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_aptoAndar")))
                        imovelVO.aptoAndar = reader.GetInt32(reader.GetOrdinal("imo_aptoAndar"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_aptoPos")))
                        imovelVO.aptoPos = reader.GetString(reader.GetOrdinal("imo_aptoPos"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_condominio")))
                        imovelVO.condominio = reader.GetString(reader.GetOrdinal("imo_condominio"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_zelador")))
                        imovelVO.zelador = reader.GetString(reader.GetOrdinal("imo_zelador"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_quartos")))
                        imovelVO.quartos = reader.GetInt32(reader.GetOrdinal("imo_quartos"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_suites")))
                        imovelVO.suites = reader.GetInt32(reader.GetOrdinal("imo_suites"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_areaTotal")))
                        imovelVO.areaTotal = reader.GetDecimal(reader.GetOrdinal("imo_areaTotal"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_areaUtil")))
                        imovelVO.areaUtil = reader.GetDecimal(reader.GetOrdinal("imo_areaUtil"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_vagasGaragem")))
                        imovelVO.vagasGaragem = reader.GetInt32(reader.GetOrdinal("imo_vagasGaragem"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_vago")))
                        imovelVO.vago = reader.GetString(reader.GetOrdinal("imo_vago"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_face")))
                        imovelVO.face = reader.GetString(reader.GetOrdinal("imo_face"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_financiamento")))
                        imovelVO.financiamento = reader.GetString(reader.GetOrdinal("imo_financiamento"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_fGTS")))
                        imovelVO.fGTS = reader.GetString(reader.GetOrdinal("imo_fGTS"));
                    string permutatmp = reader.GetString(reader.GetOrdinal("imo_permuta"));
                    if (!string.IsNullOrEmpty(permutatmp))
                    {
                        imovelVO.permuta = reader.GetString(reader.GetOrdinal("imo_permuta")).Equals("S");
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_vlrCond")))
                        imovelVO.vlrCond = reader.GetDecimal(reader.GetOrdinal("imo_vlrCond"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_placa")))
                        imovelVO.placa = reader.GetString(reader.GetOrdinal("imo_placa"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_chaves")))
                        imovelVO.chaves = reader.GetString(reader.GetOrdinal("imo_chaves"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_visitas")))
                        imovelVO.visitas = reader.GetString(reader.GetOrdinal("imo_visitas"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_obs")))
                        imovelVO.obs = reader.GetString(reader.GetOrdinal("imo_obs"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_idPromotor")))
                        imovelVO.idPromotor = reader.GetInt32(reader.GetOrdinal("imo_idPromotor"));
                    if (!reader.IsDBNull(reader.GetOrdinal("pes_nome")))
                        imovelVO.idPromotorVO.nome = reader.GetString(reader.GetOrdinal("pes_nome"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_idIndicador")))
                        imovelVO.idIndicador = reader.GetInt32(reader.GetOrdinal("imo_idIndicador"));
                    if (!reader.IsDBNull(reader.GetOrdinal("pes2_nome")))
                        imovelVO.idIndicadorVO.nome = reader.GetString(reader.GetOrdinal("pes2_nome"));
                    imovelVO.idCliente = reader.GetInt32(reader.GetOrdinal("imo_idCliente"));
                    if (!reader.IsDBNull(reader.GetOrdinal("pes3_nome")))
                        imovelVO.idClienteVO.nome = reader.GetString(reader.GetOrdinal("pes3_nome"));
                    imovelVO.vda = reader.GetString(reader.GetOrdinal("imo_vda")).Equals("S");
                    imovelVO.loc = reader.GetString(reader.GetOrdinal("imo_loc")).Equals("S");
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_vlrVda")))
                        imovelVO.vlrVda = reader.GetDecimal(reader.GetOrdinal("imo_vlrVda"));
                    if (!reader.IsDBNull(reader.GetOrdinal("imo_vlrLoc")))
                        imovelVO.vlrLoc = reader.GetDecimal(reader.GetOrdinal("imo_vlrLoc"));
                    imovelVO.idImovelCat = reader.GetInt32(reader.GetOrdinal("imo_idImovelCat"));
                    imovelVO.destaque = reader.GetChar(reader.GetOrdinal("imo_destaque"));
                    imovelVO.cadastro = reader.GetDateTime(reader.GetOrdinal("imo_Cadastro"));
                    imovelVO.cadastradoPor = reader.GetString(reader.GetOrdinal("imo_CadastradoPor"));
                    imovelVO.edicao = reader.GetDateTime(reader.GetOrdinal("imo_Edicao"));
                    imovelVO.editadoPor = reader.GetString(reader.GetOrdinal("imo_EditadoPor"));


                    listImovel.Add(imovelVO);
                }
                reader.Close();
                return listImovel;
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
        }

		public static void setImovel(ImovelVO imovelVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("UPDATE imovel ");
            query.AppendLine("SET titulo=@titulo, descricao=@descricao, idLogradouro=@idLogradouro, endNum=@endNum, endCom=@endCom, imediacoes=@imediacoes, andares=@andares, aptoAndar=@aptoAndar, aptoPos=@aptoPos, condominio=@condominio, zelador=@zelador, quartos=@quartos, suites=@suites, areaTotal=@areaTotal, areaUtil=@areaUtil, vagasGaragem=@vagasGaragem, vago=@vago, face=@face, financiamento=@financiamento, fGTS=@fGTS, permuta=@permuta, vlrCond=@vlrCond, placa=@placa, chaves=@chaves, visitas=@visitas, obs=@obs, idPromotor=@idPromotor, idIndicador=@idIndicador, idCliente=@idCliente, vda=@vda, loc=@loc, vlrVda=@vlrVda, vlrLoc=@vlrLoc, idImovelCat=@idImovelCat, destaque=@destaque, ativo=@ativo,  Cadastro=@Cadastro, CadastradoPor=@CadastradoPor, Edicao=@Edicao, EditadoPor=@EditadoPor ");
			query.AppendLine("WHERE idImovel=@idImovel ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovel", imovelVO.idImovel));
			cmd.Parameters.Add(getParameter(cmd, "@titulo", imovelVO.titulo));
			cmd.Parameters.Add(getParameter(cmd, "@descricao", imovelVO.descricao));
            cmd.Parameters.Add(getParameter(cmd, "@ativo", (imovelVO.ativo == null ? " " : (imovelVO.ativo.Value ? "S" : "N"))));
            cmd.Parameters.Add(getParameter(cmd, "@idLogradouro", imovelVO.idLogradouro));
			if (imovelVO.endNum == null)
				cmd.Parameters.Add(getParameter(cmd, "@endNum", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@endNum", imovelVO.endNum));
			if (imovelVO.endCom == null)
				cmd.Parameters.Add(getParameter(cmd, "@endCom", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@endCom", imovelVO.endCom));
			if (imovelVO.imediacoes == null)
				cmd.Parameters.Add(getParameter(cmd, "@imediacoes", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@imediacoes", imovelVO.imediacoes));
			if (imovelVO.andares == null)
				cmd.Parameters.Add(getParameter(cmd, "@andares", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@andares", imovelVO.andares));
			if (imovelVO.aptoAndar == null)
				cmd.Parameters.Add(getParameter(cmd, "@aptoAndar", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@aptoAndar", imovelVO.aptoAndar));
			if (imovelVO.aptoPos == null)
				cmd.Parameters.Add(getParameter(cmd, "@aptoPos", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@aptoPos", imovelVO.aptoPos));
			if (imovelVO.condominio == null)
				cmd.Parameters.Add(getParameter(cmd, "@condominio", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@condominio", imovelVO.condominio));
			if (imovelVO.zelador == null)
				cmd.Parameters.Add(getParameter(cmd, "@zelador", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@zelador", imovelVO.zelador));
			if (imovelVO.quartos == null)
				cmd.Parameters.Add(getParameter(cmd, "@quartos", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@quartos", imovelVO.quartos));
			if (imovelVO.suites == null)
				cmd.Parameters.Add(getParameter(cmd, "@suites", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@suites", imovelVO.suites));
			if (imovelVO.areaTotal == null)
				cmd.Parameters.Add(getParameter(cmd, "@areaTotal", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@areaTotal", imovelVO.areaTotal));
			if (imovelVO.areaUtil == null)
				cmd.Parameters.Add(getParameter(cmd, "@areaUtil", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@areaUtil", imovelVO.areaUtil));
			if (imovelVO.vagasGaragem == null)
				cmd.Parameters.Add(getParameter(cmd, "@vagasGaragem", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vagasGaragem", imovelVO.vagasGaragem));
			if (imovelVO.vago == null)
				cmd.Parameters.Add(getParameter(cmd, "@vago", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vago", imovelVO.vago));
			if (imovelVO.face == null)
				cmd.Parameters.Add(getParameter(cmd, "@face", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@face", imovelVO.face));
			if (imovelVO.financiamento == null)
				cmd.Parameters.Add(getParameter(cmd, "@financiamento", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@financiamento", imovelVO.financiamento));
			if (imovelVO.fGTS == null)
				cmd.Parameters.Add(getParameter(cmd, "@fGTS", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@fGTS", imovelVO.fGTS));
			cmd.Parameters.Add(getParameter(cmd, "@permuta", (imovelVO.permuta==null?" ":(imovelVO.permuta.Value?"S":"N"))));
			if (imovelVO.vlrCond == null)
				cmd.Parameters.Add(getParameter(cmd, "@vlrCond", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vlrCond", imovelVO.vlrCond));
			if (imovelVO.placa == null)
				cmd.Parameters.Add(getParameter(cmd, "@placa", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@placa", imovelVO.placa));
			if (imovelVO.chaves == null)
				cmd.Parameters.Add(getParameter(cmd, "@chaves", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@chaves", imovelVO.chaves));
			if (imovelVO.visitas == null)
				cmd.Parameters.Add(getParameter(cmd, "@visitas", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@visitas", imovelVO.visitas));
			if (imovelVO.obs == null)
				cmd.Parameters.Add(getParameter(cmd, "@obs", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@obs", imovelVO.obs));
			if (imovelVO.idPromotor == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPromotor", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPromotor", imovelVO.idPromotor));
			if (imovelVO.idIndicador == null)
				cmd.Parameters.Add(getParameter(cmd, "@idIndicador", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idIndicador", imovelVO.idIndicador));
			cmd.Parameters.Add(getParameter(cmd, "@idCliente", imovelVO.idCliente));
			cmd.Parameters.Add(getParameter(cmd, "@vda", (imovelVO.vda==null?" ":(imovelVO.vda.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@loc", (imovelVO.loc==null?" ":(imovelVO.loc.Value?"S":"N"))));
			if (imovelVO.vlrVda == null)
				cmd.Parameters.Add(getParameter(cmd, "@vlrVda", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vlrVda", imovelVO.vlrVda));
			if (imovelVO.vlrLoc == null)
				cmd.Parameters.Add(getParameter(cmd, "@vlrLoc", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vlrLoc", imovelVO.vlrLoc));
			cmd.Parameters.Add(getParameter(cmd, "@idImovelCat", imovelVO.idImovelCat));
            cmd.Parameters.Add(getParameter(cmd, "@destaque", imovelVO.destaque));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", imovelVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", imovelVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", imovelVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", imovelVO.editadoPor));

			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static long newImovel(ImovelVO imovelVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("INSERT INTO imovel ");
			query.AppendLine("  ( titulo, descricao, ativo, idLogradouro, endNum, endCom, imediacoes, andares, aptoAndar, aptoPos, condominio, zelador, quartos, suites, areaTotal, areaUtil, vagasGaragem, vago, face, financiamento, fGTS, permuta, vlrCond, placa, chaves, visitas, obs, idPromotor, idIndicador, idCliente, vda, loc, vlrVda, vlrLoc, idImovelCat, destaque, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
			query.AppendLine("VALUES ( @titulo, @descricao, @ativo, @idLogradouro, @endNum, @endCom, @imediacoes, @andares, @aptoAndar, @aptoPos, @condominio, @zelador, @quartos, @suites, @areaTotal, @areaUtil, @vagasGaragem, @vago, @face, @financiamento, @fGTS, @permuta, @vlrCond, @placa, @chaves, @visitas, @obs, @idPromotor, @idIndicador, @idCliente, @vda, @loc, @vlrVda, @vlrLoc, @idImovelCat, @destaque, @Cadastro, @CadastradoPor, @Edicao, @EditadoPor ) ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@titulo", imovelVO.titulo));
			cmd.Parameters.Add(getParameter(cmd, "@descricao", imovelVO.descricao));
            cmd.Parameters.Add(getParameter(cmd, "@ativo", (imovelVO.ativo == null ? " " : (imovelVO.ativo.Value ? "S" : "N"))));
            cmd.Parameters.Add(getParameter(cmd, "@idLogradouro", imovelVO.idLogradouro));
			if (imovelVO.endNum == null)
				cmd.Parameters.Add(getParameter(cmd, "@endNum", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@endNum", imovelVO.endNum));
			if (imovelVO.endCom == null)
				cmd.Parameters.Add(getParameter(cmd, "@endCom", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@endCom", imovelVO.endCom));
			if (imovelVO.imediacoes == null)
				cmd.Parameters.Add(getParameter(cmd, "@imediacoes", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@imediacoes", imovelVO.imediacoes));
			if (imovelVO.andares == null)
				cmd.Parameters.Add(getParameter(cmd, "@andares", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@andares", imovelVO.andares));
			if (imovelVO.aptoAndar == null)
				cmd.Parameters.Add(getParameter(cmd, "@aptoAndar", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@aptoAndar", imovelVO.aptoAndar));
			if (imovelVO.aptoPos == null)
				cmd.Parameters.Add(getParameter(cmd, "@aptoPos", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@aptoPos", imovelVO.aptoPos));
			if (imovelVO.condominio == null)
				cmd.Parameters.Add(getParameter(cmd, "@condominio", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@condominio", imovelVO.condominio));
			if (imovelVO.zelador == null)
				cmd.Parameters.Add(getParameter(cmd, "@zelador", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@zelador", imovelVO.zelador));
			if (imovelVO.quartos == null)
				cmd.Parameters.Add(getParameter(cmd, "@quartos", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@quartos", imovelVO.quartos));
			if (imovelVO.suites == null)
				cmd.Parameters.Add(getParameter(cmd, "@suites", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@suites", imovelVO.suites));
			if (imovelVO.areaTotal == null)
				cmd.Parameters.Add(getParameter(cmd, "@areaTotal", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@areaTotal", imovelVO.areaTotal));
			if (imovelVO.areaUtil == null)
				cmd.Parameters.Add(getParameter(cmd, "@areaUtil", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@areaUtil", imovelVO.areaUtil));
			if (imovelVO.vagasGaragem == null)
				cmd.Parameters.Add(getParameter(cmd, "@vagasGaragem", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vagasGaragem", imovelVO.vagasGaragem));
			if (imovelVO.vago == null)
				cmd.Parameters.Add(getParameter(cmd, "@vago", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vago", imovelVO.vago));
			if (imovelVO.face == null)
				cmd.Parameters.Add(getParameter(cmd, "@face", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@face", imovelVO.face));
			if (imovelVO.financiamento == null)
				cmd.Parameters.Add(getParameter(cmd, "@financiamento", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@financiamento", imovelVO.financiamento));
			if (imovelVO.fGTS == null)
				cmd.Parameters.Add(getParameter(cmd, "@fGTS", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@fGTS", imovelVO.fGTS));
			cmd.Parameters.Add(getParameter(cmd, "@permuta", (imovelVO.permuta==null?" ":(imovelVO.permuta.Value?"S":"N"))));
			if (imovelVO.vlrCond == null)
				cmd.Parameters.Add(getParameter(cmd, "@vlrCond", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vlrCond", imovelVO.vlrCond));
			if (imovelVO.placa == null)
				cmd.Parameters.Add(getParameter(cmd, "@placa", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@placa", imovelVO.placa));
			if (imovelVO.chaves == null)
				cmd.Parameters.Add(getParameter(cmd, "@chaves", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@chaves", imovelVO.chaves));
			if (imovelVO.visitas == null)
				cmd.Parameters.Add(getParameter(cmd, "@visitas", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@visitas", imovelVO.visitas));
			if (imovelVO.obs == null)
				cmd.Parameters.Add(getParameter(cmd, "@obs", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@obs", imovelVO.obs));
			if (imovelVO.idPromotor == null)
				cmd.Parameters.Add(getParameter(cmd, "@idPromotor", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idPromotor", imovelVO.idPromotor));
			if (imovelVO.idIndicador == null)
				cmd.Parameters.Add(getParameter(cmd, "@idIndicador", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@idIndicador", imovelVO.idIndicador));
			cmd.Parameters.Add(getParameter(cmd, "@idCliente", imovelVO.idCliente));
			cmd.Parameters.Add(getParameter(cmd, "@vda", (imovelVO.vda==null?" ":(imovelVO.vda.Value?"S":"N"))));
			cmd.Parameters.Add(getParameter(cmd, "@loc", (imovelVO.loc==null?" ":(imovelVO.loc.Value?"S":"N"))));
			if (imovelVO.vlrVda == null)
				cmd.Parameters.Add(getParameter(cmd, "@vlrVda", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vlrVda", imovelVO.vlrVda));
			if (imovelVO.vlrLoc == null)
				cmd.Parameters.Add(getParameter(cmd, "@vlrLoc", System.DBNull.Value));
			else
				cmd.Parameters.Add(getParameter(cmd, "@vlrLoc", imovelVO.vlrLoc));
			cmd.Parameters.Add(getParameter(cmd, "@idImovelCat", imovelVO.idImovelCat));
            cmd.Parameters.Add(getParameter(cmd, "@destaque", imovelVO.destaque));
			cmd.Parameters.Add(getParameter(cmd, "@Cadastro", imovelVO.cadastro));
			cmd.Parameters.Add(getParameter(cmd, "@CadastradoPor", imovelVO.cadastradoPor));
			cmd.Parameters.Add(getParameter(cmd, "@Edicao", imovelVO.edicao));
			cmd.Parameters.Add(getParameter(cmd, "@EditadoPor", imovelVO.editadoPor));


			try
			{
				cmd.ExecuteNonQuery();

				long newID = 0;
				if (tipo.Equals(DBType.MSSql))
				{
					cmd.CommandText = "SELECT IDENT_CURRENT('imovel') AS 'Identity' ";
					DbDataReader reader = cmd.ExecuteReader();
					reader.Read();
					newID = (long)reader.GetDecimal(reader.GetOrdinal("Identity"));
					reader.Close();
				}
				else if (tipo.Equals(DBType.MySql))
				{
					newID = ((MySql.Data.MySqlClient.MySqlCommand)cmd).LastInsertedId;
				}
				else if (tipo.Equals(DBType.ODBC))
				{
					//TODO implementar
				}
				else if (tipo.Equals(DBType.ORACLE))
				{
					//TODO implementar
				}
				return newID;
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}

		public static void delImovel(ImovelVO imovelVO)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("DELETE FROM imovel ");
			query.AppendLine("WHERE idImovel=@idImovel ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@idImovel", imovelVO.idImovel));


			try
			{
				cmd.ExecuteNonQuery();
			}
			catch (Exception ex) { throw ex; }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }

		}
        public static int getCountImovelxIdLogradouro(int? idLogradouro)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT COUNT(idLogradouro) as ocorrencias FROM imovel WHERE IdLogradouro=@IdLogradouro ");

            DbCommand cmd = getCommand();
            cmd.CommandText = query.ToString();

            cmd.Parameters.Add(getParameter(cmd, "@IdLogradouro", idLogradouro));

            DbDataReader reader = null;
            try
            {
                reader = cmd.ExecuteReader();
                reader.Read();

                int ocorrencias = 0;
                ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
                reader.Close();
                return ocorrencias;
            }
            catch (Exception ex) { throw ex; }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
        }
        public static int getCountImovelxIdPromotor(int? idPromotor)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idPromotor) as ocorrencias FROM imovel WHERE IdPromotor=@IdPromotor ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdPromotor", idPromotor));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}
		public static int getCountImovelxIdIndicador(int? idIndicador)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idIndicador) as ocorrencias FROM imovel WHERE IdIndicador=@IdIndicador ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdIndicador", idIndicador));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}
		public static int getCountImovelxIdCliente(int? idCliente)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idCliente) as ocorrencias FROM imovel WHERE IdCliente=@IdCliente ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdCliente", idCliente));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}
		public static int getCountImovelxIdImovelCat(int? idImovelCat)
		{
			StringBuilder query = new StringBuilder();

			query.AppendLine("SELECT COUNT(idImovelCat) as ocorrencias FROM imovel WHERE IdImovelCat=@IdImovelCat ");

			DbCommand cmd = getCommand();
			cmd.CommandText = query.ToString();

			cmd.Parameters.Add(getParameter(cmd, "@IdImovelCat", idImovelCat));

			DbDataReader reader = null;
			try
			{
				reader = cmd.ExecuteReader();
				reader.Read();

				int ocorrencias = 0;
				ocorrencias = reader.GetInt32(reader.GetOrdinal("ocorrencias"));
				reader.Close();
				return ocorrencias;
			}
			catch (Exception ex) { throw ex; }
			finally
			{
				if (reader != null && !reader.IsClosed)
					reader.Close();
                if (cmd != null && cmd.Connection != null)
                    cmd.Connection.Close();
            }
		}
	}
}
